create external table spmi_dm.dm_volume_cost_network_sum_m (
waybill_month                              string            comment '寄件月份',
-- entry_mark_month                           string            comment '入账月份',  -- 1.8期 delete
send_network_code                          string            comment '寄件网点编码',
send_financial_center_code                 string            comment '寄件代理区编码',
destination_province_id                    string            comment '目的省份ID',
send_network_name                          string            comment '寄件网点名称',
send_financial_center_name                 string            comment '寄件代理区名称',
destination_province_name                  string            comment '目的省份名称',
send_vote_sum                              bigint            comment '寄件票数合计',
send_weight_sum                            decimal(21,6)     comment '寄件重量合计',
single_weight                              decimal(12,6)     comment '单票重量',
single_face_sheet                          decimal(12,6)     comment '单票面单收入',
single_piece_pay                           decimal(12,6)     comment '单票派送费收入',
single_operation_pay                       decimal(12,6)     comment '单票操作费收入',
single_transfer_pay                        decimal(12,6)     comment '单票中转费收入',
single_fourcost_pay                        decimal(12,6)     comment '单票四费收入',
single_subsidy_cost                        decimal(12,6)     comment '单票激励补贴',
single_dispatch_cost                       decimal(12,6)     comment '单票派件成本',
single_net_income                          decimal(12,6)     comment '单票净收入',
single_capacity_cost                       decimal(12,6)     comment '单票操作人力成本',
single_labor_cost                          decimal(12,6)     comment '单票运力成本',
single_contribution                        decimal(12,6)     comment '单票边际贡献',
row_id                                     bigint            comment '唯一id,同一个统计维度',
statistics_flag                            smallint          comment '统计维度：1为省份维度,2为网点维度',  -- 1.8期 add
waybill_date_start                         string            comment '寄件月份起始日'
)
comment '量本利网点月汇总表'
partitioned by (mt string comment '运单生成月份 (yyyy-mm)' )
stored as parquet
location '/dw/hive/spmi_dm.db/external/dm_volume_cost_network_sum_m'
tblproperties (
  'parquet.column.index.access'='true'
);
msck repair table spmi_dm.dm_volume_cost_network_sum_m ;

drop table spmi_dws.dws_volume_cost_network_sum_m ;
CREATE TABLE spmi_dws.dws_volume_cost_network_sum_m (
  `statistics_flag` smallint NOT NULL COMMENT "统计维度：1为省份维度,2为网点维度",
  `row_id` bigint(20) NULL COMMENT "唯一主键",
  `waybill_month` varchar(30) NULL COMMENT "寄件月份",
  `send_network_code` varchar(80) NULL COMMENT "寄件网点编码",
  `send_financial_center_code` varchar(80) NULL COMMENT "寄件代理区编码",
  `destination_province_id` varchar(80) NULL COMMENT "目的省份ID",
  `waybill_date_start` date NOT NULL COMMENT "寄件月份起始日",
  `send_network_name` varchar(80) NULL COMMENT "寄件网点名称",
  `send_financial_center_name` varchar(80) NULL COMMENT "寄件代理区名称",
  `destination_province_name` varchar(80) NULL COMMENT "目的省份名称",
  `send_vote_sum` bigint(20) NULL COMMENT "寄件票数合计",
  `send_weight_sum` decimal(21, 6) NULL COMMENT "寄件重量合计",
  `single_weight` decimal(12, 6) NULL COMMENT "单票重量",
  `single_face_sheet` decimal(12, 6) NULL COMMENT "单票面单收入",
  `single_piece_pay` decimal(12, 6) NULL COMMENT "单票派送费收入",
  `single_operation_pay` decimal(12, 6) NULL COMMENT "单票操作费收入",
  `single_transfer_pay` decimal(12, 6) NULL COMMENT "单票中转费收入",
  `single_fourcost_pay` decimal(12, 6) NULL COMMENT "单票四费收入",
  `single_subsidy_cost` decimal(12, 6) NULL COMMENT "单票激励补贴",
  `single_dispatch_cost` decimal(12, 6) NULL COMMENT "单票派件成本",
  `single_net_income` decimal(12, 6) NULL COMMENT "单票净收入",
  `single_capacity_cost` decimal(12, 6) NULL COMMENT "单票操作人力成本 ",
  `single_labor_cost` decimal(12, 6) NULL COMMENT "单票运力成本",
  `single_contribution` decimal(12, 6) NULL COMMENT "单票边际贡献"
) ENGINE=OLAP
DUPLICATE KEY(`statistics_flag`,`row_id`, `waybill_month`, `send_network_code`, `send_financial_center_code`,`destination_province_id`)
COMMENT "量本利基础汇总表"
PARTITION BY RANGE(`waybill_date_start`)
(
PARTITION p202104 VALUES [('2021-04-01'), ('2021-05-01')),
PARTITION p202105 VALUES [('2021-05-01'), ('2021-06-01')),
PARTITION p202106 VALUES [('2021-06-01'), ('2021-07-01')),
PARTITION p202107 VALUES [('2021-07-01'), ('2021-08-01')),
PARTITION p202108 VALUES [('2021-08-01'), ('2021-09-01')),
PARTITION p202109 VALUES [('2021-09-01'), ('2021-10-01')),
PARTITION p202110 VALUES [('2021-10-01'), ('2021-11-01')),
PARTITION p202111 VALUES [('2021-11-01'), ('2021-12-01')),
PARTITION p202112 VALUES [('2021-12-01'), ('2022-01-01')),
PARTITION p202201 VALUES [('2022-01-01'), ('2022-02-01')),
PARTITION p202202 VALUES [('2022-02-01'), ('2022-03-01')),
PARTITION p202203 VALUES [('2022-03-01'), ('2022-04-01')))
DISTRIBUTED BY HASH(`send_network_code`) BUCKETS 6
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "MONTH",
"dynamic_partition.start" = "-12",
"dynamic_partition.end" = "2",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "6",
"in_memory" = "false",
"storage_format" = "v2"
);